﻿/*
 * doctor表的数据访问层
 * */
using System;
using System.Collections.Generic;
using System.Text;
using WH.NurseSys.Model;
using System.Data.SqlClient;
using System.Data;

namespace WH.NurseSys.DAL
{
    public class DoctorDAL
    {
        public bool Insert(Doctor doctor)
        {
            string sqlText = "insert into doctors (id,username,nickname,pwd,add_date,dept_id,rank) values(@id,@username,@nickname,@pwd,@addDate,@deptId,@rank)";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@id",doctor.Id),
                new SqlParameter("@username",doctor.Username),
                new SqlParameter("@nickname",doctor.Nickname),
                new SqlParameter("@pwd",doctor.Pwd),
                new SqlParameter("@addDate",doctor.AddDate),
                new SqlParameter("@deptId",doctor.DeptId),
                new SqlParameter("@rank",doctor.Rank),
            };
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }

        public bool Delete(Guid id)
        {
            string sqlText = "delete from doctors where id=@id";
            SqlParameter parameters = new SqlParameter("@id", id);
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }

        public List<Doctor> GetList()
        {
            string sqlText = "select id,username,nickname,pwd,add_date,dept_id,rank from doctors";
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText);
            List<Doctor> doctorList = new List<Doctor>();
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Doctor doctor = new Doctor();
                doctor.Id = (Guid)dbRow["id"];
                doctor.Username = dbRow["username"].ToString();
                doctor.Nickname = dbRow["nickname"].ToString();
                doctor.Pwd = dbRow["pwd"].ToString();
                doctor.Rank = (int)dbRow["rank"];
                doctor.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(doctor.DeptId);
                doctor.DeptName = dept.Name;
                doctor.AddDate = (DateTime)dbRow["add_date"];
                doctorList.Add(doctor);
            }
            return doctorList;
        }

        //按查询用户名/昵称返回分页列表
        public List<Doctor> GetPagedList(int pageIndex, int pageSize, string query)
        {
            int rowStart = (pageIndex - 1) * pageSize;
            int rowEnd = pageIndex * pageSize;
            string sqlText = "select * from (select row_number() over (order by add_date asc) row_num,id,username,nickname,pwd,add_date,";
            sqlText += "dept_id,rank from doctors) t where t.row_num>@rowStart and t.row_num<=@rowEnd order by add_date asc";
            if (!string.IsNullOrEmpty(query))
            {
                sqlText = "select * from (select row_number() over (order by add_date asc) row_num,id,username,nickname,pwd,add_date,";
                sqlText += "dept_id,rank from doctors where nickname like @nickname) t where t.row_num>@rowStart and t.row_num<=@rowEnd order by add_date asc";
            }
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@rowStart",rowStart),
                new SqlParameter("@rowEnd",rowEnd),
                new SqlParameter("@nickname",SqlHelper.ToDbValue(query))
            };
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            List<Doctor> doctorList = new List<Doctor>();
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Doctor doctor = new Doctor();
                doctor.Id = (Guid)dbRow["id"];
                doctor.Username = dbRow["username"].ToString();
                doctor.Nickname = dbRow["nickname"].ToString();
                doctor.Pwd = dbRow["pwd"].ToString();
                doctor.Rank = (int)dbRow["rank"];
                doctor.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(doctor.DeptId);
                doctor.DeptName = dept.Name;
                doctor.AddDate = (DateTime)dbRow["add_date"];
                doctorList.Add(doctor);
            }
            return doctorList;
        }

        public Doctor GetByUsername(string username)
        {
            string sqlText = "select id,username,nickname,pwd,add_date,dept_id,rank from doctors where username=@username";
            SqlParameter parameters = new SqlParameter("@username", username);
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            if (dbTable.Rows.Count <= 0)
                return null;
            else
            {
                DeptDAL deptDAL = new DeptDAL();
                DataRow dbRow = dbTable.Rows[0];
                Doctor doctor = new Doctor();
                doctor.Id = (Guid)dbRow["id"];
                doctor.Username = dbRow["username"].ToString();
                doctor.Nickname = dbRow["nickname"].ToString();
                doctor.Pwd = dbRow["pwd"].ToString();
                doctor.Rank = (int)dbRow["rank"];
                doctor.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(doctor.DeptId);
                doctor.DeptName = dept.Name;
                doctor.AddDate = (DateTime)dbRow["add_date"];
                return doctor;
            }
        }

        //按id获取一个用户信息
        public Doctor GetById(Guid id)
        {
            string sqlText = "select id,username,nickname,pwd,add_date,dept_id,rank from doctors where id=@id";
            SqlParameter parameters = new SqlParameter("@id", id);
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            if (dbTable.Rows.Count <= 0)
                return null;
            else
            {
                DeptDAL deptDAL = new DeptDAL();
                DataRow dbRow = dbTable.Rows[0];
                Doctor doctor = new Doctor();
                doctor.Id = (Guid)dbRow["id"];
                doctor.Username = dbRow["username"].ToString();
                doctor.Nickname = dbRow["nickname"].ToString();
                doctor.Pwd = dbRow["pwd"].ToString();
                doctor.Rank = (int)dbRow["rank"];
                doctor.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(doctor.DeptId);
                doctor.DeptName = dept.Name;
                doctor.AddDate = (DateTime)dbRow["add_date"];
                return doctor;
            }
        }

        public bool Update(Doctor doctor)
        {
            string sqlText = "update doctors set username=@username,nickname=@nickname,pwd=@pwd,rank=@rank,dept_id=@deptId,add_date=@addDate where id=@id";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@id",doctor.Id),
                new SqlParameter("@uesrname",doctor.Username),
                new SqlParameter("@nickname",doctor.Nickname),
                new SqlParameter("@pwd",doctor.Pwd),
                new SqlParameter("@addDate",doctor.AddDate),
                new SqlParameter("@deptId",doctor.DeptId),
                new SqlParameter("@rank",doctor.Rank),
            };
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }
    }
}
